package com.authine.cloudpivot.engine.repository;

import com.authine.cloudpivot.engine.domain.organization.User;
import io.swagger.v3.oas.annotations.Operation;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import javax.ws.rs.GET;
import javax.ws.rs.Path;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.*;

/**
 * @description:
 * @author: zyj
 * @time: 2021/6/17 17:47
 */
public interface TaskReportRepository<resultType, id> extends BaseRepository<User, Serializable>  {

    @Query(nativeQuery = true, value = "select count(ifnull(p.state,1)) as count,p.state,ifnull(p.QcName,'') as QC_name,p.project_name,p.QC_task_name,p.djxq from iden7_ProjectTaskIssue p group by p.state,p.QcName,p.project_name,p.QC_task_name,p.djxq")
    List<Map<String, Object>> getTaskQCReport();

    @Query(nativeQuery = true, value = "select round(storeCount) as storeCount from iden7_djxq where id = :id")
    Map<String, Object> getStoreCount(@Param(value = "id") String id);

    @Query(nativeQuery = true, value = "select id,subjectName,project_name,djRound,creater,subjectPerson from iden7_djxq where state != '已结案'")
    List<Map<String, String>> getProjects();

    @Query(nativeQuery = true, value = "select id from h_org_user where name = 'admin'")
    Map<String, String> getAdminId();

    @Query(nativeQuery = true, value = "select name from h_org_user where id = :id")
    Map<String, String> getName(@Param(value = "id") String id);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_djxq set uploading_questionnaire_nmb = :var1,end_questionnaire_nmb = :var2,followedNumber = :var3,invalidNumber = :var4,visit_end_probability = :var5,audit_end_probability = :var6,notAudit = :var7,nosub_sum = :var8 where id = :id")
    Integer updateQCTask(@Param(value = "var1") Integer var1,@Param(value = "var2") Integer var2,@Param(value = "var3") Integer var3,@Param(value = "var4") Integer var4,@Param(value = "var5") Double var5,@Param(value = "var6") Double var6,@Param(value = "id") String id,@Param(value = "var7") Integer var7,@Param(value = "var8") Integer var8);

    @Query(nativeQuery = true, value = "select count(ifnull(p.questionnaireStatus,1)) as count,p.task_name,p.questionnaireStatus,ifnull(p.QC_name,'') as QC_name,p.relevance_task_name from iden7_task_down_send p group by p.questionnaireStatus,p.QC_name,p.relevance_task_name")
    List<Map<String, Object>> getTaskZiJianQCReport();

    @Modifying
    @Query(nativeQuery = true , value = "delete from iden7_self_task_send")
    Integer deleteZiJianTask();

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_self_task_send set QC_name = :qcName where id = :id")
    Integer updateZiJianTaskSendQcName(@Param(value = "qcName") String qcName,@Param(value = "id") String id);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_self_task set add_sudit_number = :var1,end_audit_sample = :var2,stay_number = :var3,invalid_number = :var4,sudit_end_probability = :var6,notAudit = :var7 where id = :id")
    Integer updateZiJianQCTask(@Param(value = "var1") Integer var1,@Param(value = "var2") Integer var2,@Param(value = "var3") Integer var3,@Param(value = "var4") Integer var4,@Param(value = "var6") Double var6,@Param(value = "id") String id,@Param(value = "var7") Integer var7);

    @Query(nativeQuery = true, value = "select id,selfTask from iden7_SelfBuiltTaskChange order by modifiedTime desc limit 1")
    Map<String, String> getUpdateSelfBuiltTask();

    @Query(nativeQuery = true, value = "select * from iden7_sampleListChange where parentId = :id")
    List<Map<String, Object>> getSampleListChange(@Param(value = "id") String id);

    @Modifying
    @Query(nativeQuery = true , value = "delete from iden7_sampleList where parentId = :id")
    Integer deleteSampleList(@Param(value = "id") String id);

    @Query(nativeQuery = true, value = "select * from iden7_QCCustomerdemandList where parentId = :id")
    List<Map<String, Object>> getQCCustomerdemandListChange(@Param(value = "id") String id);

    @Modifying
    @Query(nativeQuery = true , value = "delete from iden7_DJCustomerdemandList where parentId = :id")
    Integer deleteDJCustomerdemandList(@Param(value = "id") String id);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_demanddetails set state = :state where parentId = :recruitmentId and state in ('进行中' ,'待执行') ")
    Integer updateStoreState(String recruitmentId,String state);

    @Modifying
    @Query(nativeQuery = true , value = "delete from iden7_xxzpzbtj where recuitmentId = :recuitmentId and reportCreateTime > :currentTime")
    Integer delOffLineReport(Date currentTime, String recuitmentId);

    @Modifying
    @Query(nativeQuery = true , value = "delete from iden7_xszpzbtj where recuitmentId = :recuitmentId and reportCreateTime > :currentTime")
    void delOnLineReport(Date currentTime, String recuitmentId);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_demandDetails set demandFor = :demandFor, storeContacts = :storeContacts, contactsPhone = :contactsPhone, comment =:comment, state = :state ,stateModifiedTime = now()  where idCode = :idCode")
    void updateDemandFor(String idCode, BigDecimal demandFor, String storeContacts, String contactsPhone, String comment,String state);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_recruitmentDemand  set state = :state where id = :id")
    void updateRecruitmentState(String id, String state);

    @Query(nativeQuery = true , value = "select count(1) from  h_org_role_user where userId = :userId and roleId = :roleId")
    Integer hasRole(String userId, String roleId);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_xszpzbtj  set onlineRecruitmentNum = :onlineRecruitmentNum, projectProvideNum = :projectProvideNum, comment = :comment, state ='已复核' where id = :id and isPigeonholed = '未归档'")
    void updateOnlineReportInfo(double onlineRecruitmentNum, double projectProvideNum, String comment,String id);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_xxzpzbtj  set plannedNum = :plannedNum, finishedNum = :finishedNum, recommendNum = :recommendNum, councilor = :councilor, comment = :comment, resubmisPerson = :resubmisPerson, state ='已提交' where id = :id and isPigeonholed = '未归档'")
    void updateOffLineReport(double plannedNum, double finishedNum, double recommendNum, String councilor, String comment, String resubmisPerson, String id);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_xxzpzbtj  set MGFinishedNum = :mgFinishedNum, clientProvideNum = :clientProvideNum, projectProvideNum = :projectProvideNum, state ='已复核' where id = :id and isPigeonholed = '未归档'")
    void updateOffLineReportInfo(double mgFinishedNum, double clientProvideNum, double projectProvideNum, String id);

    @Query(nativeQuery = true , value = "select recruitmentDemand from iden7_zpxqzbgl where id  in (:idList)")
    List<String> getOnLineRecruitmentIds(List<String> idList);

    @Query(nativeQuery = true , value = "select recruitmentDemand from  iden7_xxzpxqzbgl where id in (:idList)")
    List<String> getOffLineRecruitmentIds(List<String> idList);

    @Query(nativeQuery = true , value = "select IFNULL(demandFor,0) demandFor, sum(IFNULL(communicateNum,0)) communicateNum, sum(IFNULL(inviteNum,0)) inviteNum, sum(IFNULL(toFaceNum,0)) toFaceNum, sum(IFNULL(offerNum,0)) offerNum, sum(IFNULL(entryNum,0)) entryNum  from iden7_xszpzbtj where recuitmentId  = :recuitmentId")
    Map<String, BigDecimal> getOnLineReportMgrInfo(String recuitmentId);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zpxqzbgl set communicateNum = :communicateNum, inviteNum = :inviteNum, toFaceNum = :toFaceNum, offerNum = :offerNum, entryNum = :entryNum, finishingRate = (:entryNum / :demandFor * 100) where recruitmentDemand = :recuitmentId")
    void updateOnLineReportMgrInfo(BigDecimal communicateNum, BigDecimal inviteNum, BigDecimal toFaceNum, BigDecimal offerNum, BigDecimal entryNum, BigDecimal demandFor,String recuitmentId);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_xxzpxqzbgl set recommendNum = :recommendNum, plannedNum = :plannedNum, recommendRate = :recommendRate where recruitmentDemand = :recuitmentId")
    void updateOffLineReportMgrInfo1(double recommendNum, double plannedNum, double recommendRate, String recuitmentId);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_recruitmentDemand set recommendNum = :recommendNum, plannedNum = :plannedNum, recommendRate = :recommendRate where id = :recuitmentId")
    void updateOffLineRecruitment1(double recommendNum, double plannedNum, double recommendRate, String recuitmentId);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_xxzpxqzbgl set finishedNum = :finishedNum, demandFor = :demandFor, finishingRate = :finishingRate where recruitmentDemand = :recuitmentId")
    void updateOffLineReportMgrInfo2(double finishedNum, double demandFor, double finishingRate, String recuitmentId);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_recruitmentDemand set finishedNum = :finishedNum, demandFor = :demandFor, finishingRate = :finishingRate where id = :recuitmentId")
    void updateOffLineRecruitment2(double finishedNum, double demandFor, double finishingRate, String recuitmentId);


    @Modifying
    @Query(nativeQuery = true , value = "UPDATE iden7_demanddetails as a , ( SELECT id,storeCode,recuitmentId,IFNULL(sum(recommendNum),0) as hasHiredNum from iden7_xxzpzbtj where  recuitmentId = :recuitmentId  GROUP BY storeCode) as b  set a.hasHiredNum=b.hasHiredNum where a.parentId = b.recuitmentId and a.storeCode = b.storeCode")
    void updateOffLineRecruitmentDetail11(  String recuitmentId);

    @Modifying
    @Query(nativeQuery = true , value = "UPDATE iden7_demanddetails as a , ( SELECT id,storeCode,recuitmentId,IFNULL(sum(finishedNum),0) as hasHiredNum from iden7_xxzpzbtj where  recuitmentId = :recuitmentId  GROUP BY storeCode) as b  set a.hasHiredNum=b.hasHiredNum where a.parentId = b.recuitmentId and a.storeCode = b.storeCode")
    void updateOffLineRecruitmentDetail21(  String recuitmentId);

    @Modifying
    @Query(nativeQuery = true , value = "UPDATE iden7_demanddetails as a , ( SELECT id,storeCode,recuitmentId,IFNULL(sum(MGFinishedNum+clientProvideNum+projectProvideNum),0) as hasHiredNum from iden7_xxzpzbtj where  recuitmentId = :recuitmentId  GROUP BY storeCode) as b  set a.hasHiredNum=b.hasHiredNum where a.parentId = b.recuitmentId and a.storeCode = b.storeCode")
    void updateOffLineRecruitmentDetail22(  String recuitmentId);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_recruitmentDemand set communicateNum = :communicateNum, inviteNum = :inviteNum, toFaceNum = :toFaceNum, offerNum = :offerNum, entryNum = :entryNum,  demandFor = :demandFor, finishingRate = (:entryNum / :demandFor * 100) where id = :recuitmentId")
    void updateOnLineRecruitment(BigDecimal communicateNum, BigDecimal inviteNum, BigDecimal toFaceNum, BigDecimal offerNum, BigDecimal entryNum, BigDecimal demandFor, String recuitmentId);


    @Query(nativeQuery = true , value = "select id, date_format(reportCreateTime, '%Y-%m-%d') reportCreateTime from iden7_xszpzbtj where recuitmentId  = :recuitmentId order by reportCreateTime desc ")
    ArrayList<Object[]> getOnLineReportCreateTimeList(String recuitmentId);

    @Query(nativeQuery = true , value = "select id, date_format(reportCreateTime, '%Y-%m-%d') reportCreateTime from iden7_xxzpzbtj where recuitmentId  = :recuitmentId order by reportCreateTime desc ")
    ArrayList<Object[]> getOffLineReportCreateTimeList(String recuitmentId);

    @Modifying
    @Query(nativeQuery = true , value = "delete from iden7_xszpzbtj where id in (:delIds)")
    void delOnLineWeeklyReport(List<String> delIds);

    @Modifying
    @Query(nativeQuery = true , value = "delete from iden7_xxzpzbtj where id in (:delIds)")
    void delOffLineWeeklyReport(List<String> delIds);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_xszpzbtj t, iden7_demanddetails s set t.demandFor =s.demandFor, t.needFinishTime = :needFinishTime where s.parentId = :recruitmentId and  t.postId = s.idcode and t.isPigeonholed ='未归档'")
    void updateOnLineWeeklyReportInfo(String recruitmentId, Date needFinishTime);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_xxzpzbtj t, iden7_demanddetails s set t.demandFor =s.demandFor, t.needFinishTime = :needFinishTime where s.parentId = :recruitmentId and  t.postId = s.idcode and t.isPigeonholed ='未归档'")
    void updateOffLineWeeklyReportInfo(String recruitmentId, Date needFinishTime);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zpxqzbgl t, (SELECT sum(ifnull(demandFor,0)) total from iden7_demanddetails where parentId = :recruitmentId) s set t.demandFor = s.total, t.finishingRate  = (CASE WHEN  s.total = 0 THEN 0 ELSE ifnull(t.entryNum,0)/s.total*100 END), t.needFinishTime = :needFinishTime where t.recruitmentDemand = :recruitmentId")
    void updateOnLineWeeklyReportMgrInfo(String recruitmentId, Date needFinishTime);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_xxzpxqzbgl t, (SELECT sum(ifnull(demandFor,0)) total from iden7_demanddetails where parentId = :recruitmentId) s set  t.demandFor = s.total, t.finishingRate = (CASE WHEN  s.total = 0 THEN 0 ELSE ifnull(t.finishedNum,0)/s.total*100 END ), t.needFinishTime = :needFinishTime where t.recruitmentDemand = :recruitmentId")
    void updateOffLineWeeklyReportMgrInfo1(String recruitmentId, Date needFinishTime);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_xxzpxqzbgl t, (SELECT sum(IFNULL(plannedNum,0)) total from iden7_xxzpzbtj where recuitmentId = :recruitmentId) s set  t.plannedNum = s.total, t.recommendRate = ( CASE WHEN  s.total = 0 THEN 0 ELSE IFNULL(t.recommendNum,0)/s.total*100 END ), t.needFinishTime = :needFinishTime where t.recruitmentDemand = :recruitmentId")
    void updateOffLineWeeklyReportMgrInfo2(String recruitmentId, Date needFinishTime);

    @Query(nativeQuery = true , value = "select recuitmentId,  sum(communicateNum) communicateNum, sum(inviteNum) inviteNum, sum(toFaceNum) toFaceNum, sum(offerNum) offerNum, sum(entryNum) entryNum  from iden7_xszpzbtj GROUP BY  recuitmentId")
    List<Map<String, Object>> getOnLineReportMgrInfo();

    @Modifying
    @Query(nativeQuery = true , value = "delete from iden7_xszpzbtj where postId in (:idCodes) and reportCreateTime > :nextMonday")
    void delOnLineReportByICode(List<String> idCodes, Date nextMonday);

    @Modifying
    @Query(nativeQuery = true , value = "delete from iden7_xxzpzbtj where postId in (:idCodes) and reportCreateTime > :nextMonday")
    void delOffLineReportByICode(List<String> idCodes, Date nextMonday);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_executedetails s, (select  DISTINCT province, rcsArea from iden7_XZQH) t  set s.area = t.rcsArea where s.parentId = :id and t.province like CONCAT(s.province, '%') ")
    void updateZXXQTBRCSArea(String id);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_executeDetailList s, (select  DISTINCT province, rcsArea from iden7_XZQH) t  set s.area = t.rcsArea where s.parentId = :id and t.province like CONCAT(s.province, '%') ")
    void updateZXXQBGRCSArea(String id);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_demandDetails s, (select  DISTINCT province, rcsArea from iden7_XZQH) t  set s.rcsArea = t.rcsArea where s.parentId = :id and t.province like CONCAT(s.province, '%') ")
    void updateZPXQTBRCSArea(String id);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_demandDetailList s, (select  DISTINCT province, rcsArea from iden7_XZQH) t  set s.rcsArea = t.rcsArea where s.parentId = :id and t.province like CONCAT(s.province, '%') ")
    void updateZPXQBGRCSArea(String id);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_executedetails  set  storeNum = :newStoreNum, planGearsNum = :newPlanGearsNum, planRoundNum = :newPlanRoundNum where id = :executeDetailId")
    void updateExecuteDetail(String executeDetailId, BigDecimal newStoreNum, BigDecimal newPlanGearsNum, BigDecimal newPlanRoundNum);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zxzbmx  set  storeNum = :newStoreNum, planGearsNum = :newPlanGearsNum, planRoundNum = :newPlanRoundNum where executeId = :executeDetailId")
    void updateExecuteReportInfo(String executeDetailId, BigDecimal newStoreNum, BigDecimal newPlanGearsNum, BigDecimal newPlanRoundNum);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zxzbgl s, (select sum(IFNULL(planRoundNum,0)) planRoundTotalNum,sum(IFNULL(actualRoundNum,0)) actualRoundTotalNum, sum(IFNULL(reasonNum,0)) reasonNum from iden7_zxzbmx where executeDemandId = :executeId  and reportCreateTime <= now()) t set s.activityStartTime = :activityStartTime, s.activityEndTime = :activityEndTime, s.planRoundTotalNum = t.planRoundTotalNum, s.actualRoundTotalNum =t.actualRoundTotalNum, s.roundTotalRate = (t.actualRoundTotalNum / (t.planRoundTotalNum-t.reasonNum) * 100), s.reasonNum = t.reasonNum  where copyDemand = :executeId")
    void updateExecuteMgrInfo(String executeId,Date activityStartTime,Date activityEndTime);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zxzbmx s set s.reportCreateTime = :newWeekTime where  s.executeId = :executeId and s.reportCreateTime = :oldWeekTime")
    void updateExecuteReportTime(String executeId, Date oldWeekTime, Date newWeekTime);

    @Modifying
    @Query(nativeQuery = true , value = "delete from iden7_zxzbmx where executeId in (:executeIds)")
    void delExecuteWeeklyReport(List<String> executeIds);

    @Modifying
    @Query(nativeQuery = true , value = "delete from iden7_executedetails where id in (:executeIds)")
    void delExecuteExecuteDemand(List<String> executeIds);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zxzbmx s set s.activityEndTime = :activityEndTime where s.executeDemandId = :executeDemandId")
    void updateExecuteReportEndTime(String executeDemandId, Date activityEndTime);

    @Modifying
    @Query(nativeQuery = true , value = "INSERT INTO iden7_executedetails (province, city, year, cycle, planRoundNum, storeNum, id, parentId, modifier, modifiedTime, month, planGearsNum, area, state, dateRange) VALUES ( :province, :city, :year, :cycle, :planRoundNum, :storeNum, :executeId, :executeDemandId, :modifier, :modifiedTime, :month, :planGearsNum, :area, :state, :dateRange)")
    void createExecuteDetail(Object province, Object city, Object year, Object cycle, Object planRoundNum, Object storeNum, Object executeId, Object executeDemandId, Object modifier, Object modifiedTime, Object month, Object planGearsNum, Object area, Object state, Object dateRange);

    @Query(nativeQuery = true , value = "select copyDemand from  iden7_zxzbgl  where id in (:idList)")
    List<String> getExecuteDemandIds(List<String> idList);

    @Query(nativeQuery = true , value = "select id from  iden7_recruitmentdemand t where t.state != '已结束' and t.demandEndTime < :date and recruitmentType != '按确定人数'")
    List<String> getOverTimeRecruitmentDemand(Date date);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_executedetails set state = :state  WHERE  parentId = :demandId and  year = :year and month = :month and cycle = :cycle")
    void updateDetailState(String demandId, String year, String month, String cycle, String state);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_executedetails set reportCreateTime = :reportCreateTime  WHERE  executeId = :demandId and  year = :year and month = :month and cycle = :cycle")
    void updateExecuteCreateReportTime(String demandId, String year, String month, String cycle, Date reportCreateTime);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zxxqtb set activityEndTime = :activityEndTime  WHERE  id = :demandId")
    void updateExecuteDemandEndTime(String demandId, Date activityEndTime);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zxzbmx set actualGearsNum = :actualGearsNum,actualRoundNum = :actualRoundNum,councilor = :councilor, remark = :remark, resubmisPerson = :resubmisPerson, state  ='已提交',reasonNum = :reasonNum,arePersons = :arePersons WHERE executeId = :id")
    void updateExecuteReportByOp(double actualGearsNum, double actualRoundNum, String councilor, String remark, String resubmisPerson, String id, double reasonNum,String arePersons);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zxzbmx set mgGearsNum = :mgGearsNum,mgRoundNum = :mgRoundNum,projectProvideNum = :projectProvideNum, clientProvideNum = :clientProvideNum, state  ='已复核' WHERE executeId = :id")
    void updateExecuteReportByPM(double mgGearsNum, double mgRoundNum, double projectProvideNum, double clientProvideNum, String id);

    @Query(nativeQuery = true , value = "select executeDemandId, year,month,cycle, sum(IFNULL(storeNum,0)) storeNum,sum(IFNULL(planGearsNum,0)) planGearsNum, sum(IFNULL(planRoundNum,0)) planRoundNum,sum(IFNULL( actualGearsNum,0)) actualGearsNum, sum(IFNULL(actualRoundNum,0)) actualRoundNum, (sum(IFNULL(actualGearsNum,0))/sum(IFNULL(planGearsNum,0))*100) lGearsRate, (sum(IFNULL(actualRoundNum,0))/sum(IFNULL(planRoundNum,0)-ifnull(reasonNum,0))*100) RoundRate,sum(ifnull(reasonNum,0)) as reasonNum from iden7_zxzbmx WHERE  executeDemandId in (:list) group by executeDemandId,year,month,cycle")
    List<Map<String, Object>> getExecuteReportInfo(List<String> list);
    @Query(nativeQuery = true , value = "select executeDemandId, year,month,cycle, sum(IFNULL(storeNum,0)) storeNum,sum(IFNULL(planGearsNum,0)) planGearsNum, sum(IFNULL(planRoundNum,0)) planRoundNum,sum(if(MGGearsNum,MGGearsNum,actualGearsNum)) actualGearsNum, sum(if(MGRoundNum,MGRoundNum,actualRoundNum)) actualRoundNum, (sum(if(MGGearsNum,MGGearsNum,actualGearsNum))/sum(IFNULL(planGearsNum,0))*100) lGearsRate, (sum(if(MGRoundNum,MGRoundNum,actualRoundNum))/sum(IFNULL(planRoundNum,0)-ifnull(reasonNum,0))*100) RoundRate,sum(ifnull(reasonNum,0)) as reasonNum from iden7_zxzbmx WHERE  executeDemandId in (:list) group by executeDemandId,year,month,cycle")
    List<Map<String, Object>> getExecuteReviewReportInfo(List<String> list);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zxzbmx set reportCreateTime = :reportCreateTime WHERE executeDemandId = :executeDemandId and year = :year and month = :month and cycle = :cycle")
    void updateReportCreateTime(String executeDemandId, String year, String month, String cycle, Date reportCreateTime);

    @Query(nativeQuery = true , value = "SELECT  t.creater,s.year,s.month,s.cycle,t.id parentId, s.id id,t.copyDemand FROM iden7_zxzbgl t LEFT JOIN iden7_executepool s ON t.id = s.parentId where t.copyDemand in(:list)")
    List<Map<String, Object>> getExecuteMgrInfo(List<Set<String>> list);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zxzbgl s, (select sum(IFNULL(planRoundNum,0)) planRoundNum, sum(IFNULL(actualRoundNum,0)) actualRoundNum, sum(IFNULL(reasonNum,0)) reasonNum from iden7_zxzbmx where executeDemandId = :demandId) t set s.planRoundTotalNum = t.planRoundNum, s.actualRoundTotalNum = t.actualRoundNum, s.RoundTotalRate = (t.actualRoundNum/(t.planRoundNum-t.reasonNum)*100), s.reasonNum = t.reasonNum where copyDemand = :demandId")
    void updateTotalRate(String demandId);
    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zxzbgl s, (select sum(IFNULL(planRoundNum,0)) planRoundNum, sum(IFNULL(MGRoundNum,0)) actualRoundNum, sum(IFNULL(reasonNum,0)) reasonNum from iden7_zxzbmx where executeDemandId = :demandId) t set s.planRoundTotalNum = t.planRoundNum, s.actualRoundTotalNum = t.actualRoundNum, s.RoundTotalRate = (t.actualRoundNum/(t.planRoundNum-t.reasonNum)*100), s.reasonNum = t.reasonNum where copyDemand = :demandId")
    void updateTotalRateReview(String demandId);


    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zxxqtb s, (select sum(IFNULL(planRoundNum,0)) planRoundNum, sum(IFNULL(actualRoundNum,0)) actualRoundNum, sum(IFNULL(reasonNum,0)) reasonNum from iden7_zxzbmx where executeDemandId = :demandId) t set s.planRoundTotalNum = t.planRoundNum, s.actualRoundTotalNum = t.actualRoundNum, s.RoundTotalRate = (t.actualRoundNum/(t.planRoundNum-t.reasonNum)*100), s.reasonNum = t.reasonNum where id = :demandId")
    void updateDemandTotalRate(String demandId);
    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zxxqtb s, (select sum(IFNULL(planRoundNum,0)) planRoundNum, sum(IFNULL(MGRoundNum,0)) actualRoundNum, sum(IFNULL(reasonNum,0)) reasonNum from iden7_zxzbmx where executeDemandId = :demandId) t set s.planRoundTotalNum = t.planRoundNum, s.actualRoundTotalNum = t.actualRoundNum, s.RoundTotalRate = (t.actualRoundNum/(t.planRoundNum-t.reasonNum)*100), s.reasonNum = t.reasonNum where id = :demandId")
    void updateDemandTotalRateReview(String demandId);

    @Modifying
    @Query(nativeQuery = true , value = "INSERT INTO iden7_executepool (year, month, cycle, storeNum, planGearsNum, actualGearsNum, planRoundNum, actualRoundNum, lGearsRate, roundRate, modifier, modifiedTime, parentId, id, reasonNum) VALUES ( :year, :month, :cycle, :storeNum, :planGearsNum, :actualGearsNum, :planRoundNum, :actualRoundNum, :lGearsRate, :roundRate, :modifier, :modifiedTime, :parentId, :id ,:reasonNum)")
    void insertExecutePool(Object year, Object month, Object cycle, Object storeNum, Object planGearsNum, Object actualGearsNum, Object planRoundNum, Object actualRoundNum, Object lGearsRate, Object roundRate, Object modifier, Object modifiedTime, Object parentId, String id, Object reasonNum);

    @Modifying
    @Query(nativeQuery = true , value = "UPDATE iden7_executepool set storeNum = :storeNum, planGearsNum = :planGearsNum, actualGearsNum = :actualGearsNum, planRoundNum = :planRoundNum, actualRoundNum = :actualRoundNum, lGearsRate = :lGearsRate, roundRate = :roundRate,reasonNum = :reasonNum where  id =:id")
    void updateExecutePool(Object storeNum, Object planGearsNum, Object actualGearsNum, Object planRoundNum, Object actualRoundNum, Object lGearsRate, Object roundRate, Object id, Object reasonNum);

    @Modifying
    @Query(nativeQuery = true , value = "UPDATE iden7_executepool set sortKey = (9999999 - concat(year,month,REPLACE(cycle, 'W', '')))")
    void sortExecutePool();

    @Query(nativeQuery = true , value = "select id from  iden7_zxxqtb t where t.state != '已结束' and t.activityEndTime < :date ")
    List<String> getOverTimeExecuteDemand(Date date);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zxxqtb set state = :state where id = :id")
    void updateExecuteDemandState(String id, String state);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_executedetails set state = :state where parentId = :id and (state ='执行中' or state ='待执行')")
    void updateExecuteDetailState(String id, String state);
    
    @Query(nativeQuery = true , value = "select s.idCode from iden7_demanddetails s LEFT JOIN iden7_recruitmentdemand m ON m.id = s.parentId where  m.state = '执行中' and  m.recruitmentType !='不确定人数'  AND s.state ='已完成' and s.hasHiredNum >=  s.demandFor")
    List<String> getFinishedOnLineDetails();

    @Query(nativeQuery = true , value = "SELECT s.idCode FROM iden7_demanddetails s LEFT JOIN iden7_recruitmentdemand m ON m.id = s.parentId LEFT JOIN (SELECT postId, SUM( CASE state WHEN  '已提交'  THEN finishedNum WHEN  '已复核'  THEN IFNULL( MGFinishedNum, 0 )+ IFNULL( clientProvideNum, 0 )+ IFNULL( projectProvideNum, 0) ELSE 0 END) finishedNum FROM iden7_xxzpzbtj GROUP BY postId) t ON s.idCode = t.postId WHERE m.state = '执行中' and  m.recruitmentType !='不确定人数' AND s.state ='进行中'  and t.finishedNum >= s.demandFor")
    List<String> getFinishedOffLineDetails();

    @Query(nativeQuery = true , value = "select s.idCode from iden7_demanddetails s LEFT JOIN iden7_recruitmentdemand m ON m.id = s.parentId where  m.state = '执行中' and  m.recruitmentType !='不确定人数'  AND s.state ='已完成' and s.hasHiredNum <  s.demandFor")
    List<String> getUnFinishedOnLineDetails();

    @Query(nativeQuery = true , value = "SELECT s.idCode FROM iden7_demanddetails s LEFT JOIN iden7_recruitmentdemand m ON m.id = s.parentId LEFT JOIN (SELECT postId, SUM( CASE state WHEN  '已提交'  THEN finishedNum WHEN  '已复核'  THEN IFNULL( MGFinishedNum, 0 )+ IFNULL( clientProvideNum, 0 )+ IFNULL( projectProvideNum, 0) ELSE 0 END) finishedNum FROM iden7_xxzpzbtj GROUP BY postId) t ON s.idCode = t.postId WHERE m.state = '执行中' and  m.recruitmentType !='不确定人数' AND s.state ='已完成' and t.finishedNum  < s.demandFor")
    List<String> getUnFinishedOffLineDetails();

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_demanddetails set state = :state ,stateModifiedTime=now() where state in ('进行中','待执行','已完成') and  idCode in ( :idCodes )")
    void updateRecruitmentDetailStatus(List<String> idCodes, String state);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_executedetails set state = :state where year = :year and month= :month and cycle = :cycle")
    void updateExecuteDetailStateByCycle(String year, String month, String cycle, String state);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zxzbmx s, (select area, id from iden7_executedetails where parentId = :executeDemandId) t set  s.area = t.area where s.executeId  = t.id")
    void updateExecuteRcsArea(String executeDemandId);

    @Modifying
    @Query(nativeQuery = true , value = "delete from iden7_whmdxx where zpDemand is NULL and djDemand is NULL  and zxDemand LIKE CONCAT_WS('','%', :executeId,'%') ")
    void delStoreByExecuteId(String executeId);

    @Modifying
    @Query(nativeQuery = true , value = "  update iden7_whmdxx s, (select  DISTINCT province, rcsArea from iden7_XZQH) t  set s.rcsArea = t.rcsArea where s.executeDemand = :executeId and t.province like CONCAT(s.province, '%')")
    void updateRCSAreaByExecuteId(String executeId);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zxxqtb set uploadStoreState = '是' where id = :executeId")
    void updateAttrStatusByExecuteId(String executeId);

    @Query(nativeQuery = true , value = "SELECT s.recruitmentDemand demandId, s.demandFor, t.existNum FROM iden7_zpxqzbgl s LEFT JOIN (SELECT recuitmentId, sum(IFNULL(clientProvideNum,0)) + sum(IFNULL(projectProvideNum,0)) existNum FROM iden7_xszpzbtj WHERE recuitmentId = :recruitmentId AND id NOT IN ( :ids )) t ON s.recruitmentDemand = t.recuitmentId WHERE s.recruitmentDemand = :recruitmentId")
    Map getOnLineCalculateNum(String recruitmentId, List<String> ids);

    @Query(nativeQuery = true , value = "SELECT s.recruitmentDemand demandId, s.demandFor, t.existNum FROM iden7_xxzpxqzbgl s LEFT JOIN (SELECT recuitmentId,sum(IFNULL(MGFinishedNum, IFNULL( finishedNum, 0 ))) + sum(IFNULL( clientProvideNum, 0 )) + sum(IFNULL( projectProvideNum, 0 )) existNum FROM iden7_xxzpzbtj WHERE recuitmentId = :recruitmentId AND id NOT IN ( :ids )) t ON s.recruitmentDemand = t.recuitmentId WHERE s.recruitmentDemand = :recruitmentId")
    Map getOffLineCalculateNum(String recruitmentId, List<String> ids);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_xszpzbtj set state ='已复核' where reportCreateTime >= :startTime and reportCreateTime <= :endTime")
    void autoReviewOnlineReport(Date startTime, Date endTime);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_xxzpzbtj  set state ='已复核' where reportCreateTime >= :startTime and reportCreateTime <= :endTime")
    void autoReviewOffLineReport(Date startTime, Date endTime);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zxzbmx  set state ='已复核' where reportCreateTime >= :startTime and reportCreateTime <= :endTime")
    void autoReviewExecuteReport(Date startTime, Date endTime);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_xxzpzbtj  set state = :state where state = '未提交' and reportCreateTime >= :startTime and reportCreateTime <= :endTime ")
    void updateOfflineReportState(Date startTime, Date endTime, String state);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zxzbmx  set state = :state where state = '未提交' and reportCreateTime >= :startTime and reportCreateTime <= :endTime")
    void updateExecuteReportState(Date startTime, Date endTime, String state);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_xszpzbtj set isPigeonholed = '已归档' where reportCreateTime >= :startTime and reportCreateTime <= :endTime")
    void autoArchiveOnlineReport(Date startTime, Date endTime);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_xxzpzbtj  set isPigeonholed = '已归档' where reportCreateTime >= :startTime and reportCreateTime <= :endTime")
    void autoArchiveOffLineReport(Date startTime, Date endTime);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zxzbmx  set isPigeonholed = '已归档' where reportCreateTime >= :startTime and reportCreateTime <= :endTime")
    void autoArchiveExecuteReport(Date startTime, Date endTime);

    @Query(nativeQuery = true , value = "select t.projectNames,s.postJDStr, s.operator from iden7_xxzpzbtj s, iden7_XMQD t  where t.id = s.projectName and  s.state = '未提交'  and s.reportCreateTime <= :endTime and s.reportCreateTime >= :startTime GROUP by s.projectName")
    List<Map<String, Object>> getUncommittedOffLineReport(Date startTime, Date endTime);

    @Query(nativeQuery = true , value = "select t.projectNames, s.activityName,  s.operator from iden7_zxzbmx s, iden7_XMQD t  where t.id = s.projectName and  s.state = '未提交'  and s.reportCreateTime <= :endTime and s.reportCreateTime >= :startTime GROUP by s.projectName")
    List<Map<String, Object>> getUncommittedExecuteReport(Date startTime, Date endTime);

    @Query(nativeQuery = true , value = "select t.projectNames,s.postJDStr, s.persons,s.subjectPerson,s.frontOperator from iden7_xszpzbtj s, iden7_XMQD t  where t.id = s.projectName and  s.state != '未复核'  and s.reportCreateTime <= :endTime and s.reportCreateTime >= :startTime GROUP by s.projectName")
    List<Map<String, Object>>  getUnReviewedOnlineReport(Date startTime, Date endTime);

    @Query(nativeQuery = true , value = "select t.projectNames,s.postJDStr, s.persons,s.subjectPerson,s.frontOperator from iden7_xxzpzbtj s, iden7_XMQD t  where t.id = s.projectName and  s.state != '未复核'  and s.reportCreateTime <= :endTime and s.reportCreateTime >= :startTime GROUP by s.projectName")
    List<Map<String, Object>>  getUnReviewedOffLineReport(Date startTime, Date endTime);

    @Query(nativeQuery = true , value = "select t.projectNames, s.activityName, s.persons,s.subjectPerson,s.frontOperator from iden7_zxzbmx s, iden7_XMQD t  where t.id = s.projectName and  s.state != '未复核'  and s.reportCreateTime <= :endTime and s.reportCreateTime >= :startTime GROUP by s.projectName")
    List<Map<String, Object>>  getUnReviewedExecuteReport(Date startTime, Date endTime);

    @Query(nativeQuery = true , value = "select t.projectNames,  s.postJDStr, s.operator from iden7_xxzpzbtj s, iden7_XMQD t  where t.id = s.projectName and  s.state = '未提交'  and s.reportCreateTime <= :endTime and s.reportCreateTime >= :startTime GROUP by s.projectName")
    List<Map<String, Object>> getUnArchivedOffLineReport(Date startTime, Date endTime);

    @Query(nativeQuery = true , value = "select t.projectNames, s.activityName,  s.operator from iden7_zxzbmx s, iden7_XMQD t  where t.id = s.projectName and  s.state = '未提交'  and s.reportCreateTime <= :endTime and s.reportCreateTime >= :startTime GROUP by s.projectName")
    List<Map<String, Object>> getUnArchivedExecuteReport(Date startTime, Date endTime);


    @Query(nativeQuery = true , value = "select s.id, s.subjectPerson, s.projectNames, TIMESTAMPDIFF(DAY,DATE_FORMAT(s.finishedTime, '%Y-%m-%d'),DATE_FORMAT(NOW(), '%Y-%m-%d')) days from iden7_XMQD s where s.subjectStatus = '已立项' and  s.isContract  = '否' and s.finishedTime is not null  and date_format( DATE_SUB(now(), INTERVAL :startDays DAY), '%Y-%m-%d') <= s.finishedTime and   s.finishedTime < date_format( DATE_SUB(now(), INTERVAL :endDays DAY), '%Y-%m-%d')")
    List<Map<String, Object>> getNoContractProject(int startDays, int endDays);

    @Query(nativeQuery = true , value = "select  operator from iden7_zxxqtb  where projectName = :id  and  operator  is not null UNION select  operator from iden7_recruitmentdemand  where projectName = :id and  operator  is not null")
    List<Map<String, Object>> getOperators(String id);

    @Query(nativeQuery = true , value = "SELECT recruitmentType from iden7_recruitmentdemand where id = :recuitmentId")
    String getRecruitmentType(String recuitmentId);

    @Query(nativeQuery = true , value = "SELECT postId, demandFor, plannedNum, finishedNum, recommendNum, MGFinishedNum, clientProvideNum,projectProvideNum, projectName, isPigeonholed, state FROM iden7_xxzpzbtj where recuitmentId  = :recuitmentId")
    List<Map<String, Object>> getOffLineReport(String recuitmentId);

    @Query(nativeQuery = true , value = "select  sum(demandFor) demandFor from iden7_demanddetails where parentId = :recuitmentId")
    BigDecimal getDemandFor(String recuitmentId);

    @Query(nativeQuery = true , value = "select CONCAT_WS('-', projectNameStr, postJDStr) as name , operator from iden7_recruitmentdemand where id = :demandId")
    Map<String, String> getRecruitmentDemandInfo(String demandId);

    @Query(nativeQuery = true , value = " select  CONCAT_WS('-', projectNameStr, activityName)  as name, operator from iden7_zxxqtb where id = :demandId")
    Map<String, String> getExecuteDemandInfo(String demandId);

    @Query(nativeQuery = true , value = " select userId from h_org_role_user  where roleId = :projectDirectorRoleId and ouScope like (select  CONCAT_WS('','%',deptId,'%') from h_org_dept_user where userId  = :userId)")
    String getProjectDirector(String userId, String projectDirectorRoleId);

    @Query(nativeQuery = true , value = " select projectNameStr, postJDStr, operator, demandCreatedTime, TIMESTAMPDIFF(DAY,DATE_FORMAT(NOW(), '%Y-%m-%d'),DATE_FORMAT(demandEndTime, '%Y-%m-%d')) days from iden7_recruitmentdemand where state = '执行中' and TIMESTAMPDIFF(DAY,DATE_FORMAT(NOW(), '%Y-%m-%d'),DATE_FORMAT(demandEndTime , '%Y-%m-%d')) >=0 and  TIMESTAMPDIFF(DAY,DATE_FORMAT(NOW(), '%Y-%m-%d'),DATE_FORMAT(demandEndTime , '%Y-%m-%d')) <= :days")
    List<Map<String, Object>> traceRecruitment(int days);

    @Modifying
    @Query(nativeQuery = true , value = "INSERT INTO iden7_demanddetails (parentId, storeCode, province, areas, storeName, address, area, storeChannel, storeContacts, contactsPhone, cityType, workType, isFixedWorkPlace, isStore, jobNature, scale, salaryCap, contractType, probationaryTime, probationarySalary,comment, idCode, demandFor,  modifier, modifiedTime, state, city, rcsArea, id) VALUES (:recruitmentId, :storeCode, :province, :areas, :storeName, :address, :area, :storeChannel, :storeContacts, :contactsPhone, :cityType, :workType, :isFixedWorkPlace, :isStore, :jobNature, :scale, :salaryCap, :contractType, :probationaryTime, :probationarySalary, :comment, :idCode, :demandFor,  :modifier, :modifiedTime, :state, :city, :rcsArea, :id)")
    void createRecruitmentDetail(Object recruitmentId, Object storeCode, Object province, Object areas, Object storeName, Object address, Object area, Object storeChannel, Object storeContacts, Object contactsPhone, Object cityType, Object workType, Object isFixedWorkPlace, Object isStore, Object jobNature, Object scale, Object salaryCap, Object contractType, Object probationaryTime, Object probationarySalary, Object comment, Object idCode, Object demandFor, Object modifier, Object modifiedTime, Object state, Object city, Object rcsArea, Object id);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_whmdxx  set zpDemand = ( CASE WHEN  zpDemand IS NULL THEN :demandId ELSE  CONCAT_WS(';', zpDemand, :demandId) END ), ZPCount = ifnull(ZPCount,0) + 1, storeStatus = '启用' where subjectName = :subjectName  and customerStoreNum = :storeCode  AND (zpDemand NOT LIKE CONCAT_WS('','%',:demandId,'%') or zpDemand is null)")
    void updateStoreInfoFromZP(String demandId, String subjectName, Object storeCode);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_whmdxx  set zxDemand = ( CASE WHEN  zxDemand IS NULL THEN :demandId ELSE  CONCAT_WS(';', zxDemand, :demandId) END ), zxCount = ifnull(zxCount,0) + 1, storeStatus = '启用' where subjectName = :subjectName  and customerStoreNum = :storeCode  AND (zxDemand NOT LIKE CONCAT_WS('','%',:demandId,'%') or zxDemand is null)")
    void updateStoreInfoFromZX(String demandId, String subjectName, Object storeCode);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_whmdxx  set djDemand = ( CASE WHEN  djDemand IS NULL THEN :demandId ELSE  CONCAT_WS(';', djDemand, :demandId) END ), djCount = ifnull(djCount,0) + 1, storeStatus = '启用' where subjectName = :subjectName  and customerStoreNum = :storeCode  AND (djDemand NOT LIKE CONCAT_WS('','%',:demandId,'%') or djDemand is null)")
    void updateStoreInfoFromDJ(String demandId, String subjectName, Object storeCode);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_demanddetails set state = :state where  storeCode = :customerStoreNum and state in ('进行中','待执行') and  parentId in ( :demandIds )")
    void updateDetailStoreState(List<String> demandIds, String customerStoreNum, String state);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_zpxqzbgl set demandFor = (select sum(ifnull(demandFor,0)) from  iden7_demanddetails  where parentId = :demandId ) where  recruitmentDemand = :demandId")
    void updateOnLineDemandForById(String demandId);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_xxzpxqzbgl set demandFor = (select sum(ifnull(demandFor,0)) from  iden7_demanddetails  where parentId = :demandId ) where  recruitmentDemand = :demandId")
    void updateOffLineDemandForById(String demandId);

    @Modifying
    @Query(nativeQuery = true , value = "  update iden7_whmdxx s, (select  DISTINCT province, rcsArea from iden7_XZQH) t  set s.rcsArea = t.rcsArea where s.subjectName = :subjectName and t.province like CONCAT(s.province, '%')")
    void updateStoreRCSArea(String subjectName);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_demandDetails set rcsArea = :rcsArea, province = :province, city = :cityName, areas =:district,  storeName = :storeShortName, address = :detailArea,  area = :area, storeChannel = :storeOutlet, storeContacts = :customDockingPerson, contactsPhone = :customLinkType, cityType = :cityLevel where  storeCode = :customerStoreNum and  parentId in ( :demandIds )")
    void updateZPDetail(List<String> demandIds, String storeShortName, String rcsArea, String province, String cityName, String district, String detailArea, String area, String storeOutlet, String customDockingPerson, String customLinkType, String cityLevel,String customerStoreNum);


    @Modifying
    @Query(nativeQuery = true , value = " update iden7_XSZPZBTJ set province = :province, city = :cityName, areas =:district,  storeName = :storeShortName, address = :detailArea,  area = :area, rcsArea = :rcsArea where  storeCode = :customerStoreNum and  recuitmentId in ( :demandIds )")
    void updateOnLineReportDetail(List<String> demandIds, String storeShortName, String rcsArea, String province, String cityName, String district, String detailArea, String area, String customerStoreNum);

    @Modifying
    @Query(nativeQuery = true , value = " update iden7_XXZPZBTJ set province = :province, city = :cityName, areas =:district,  storeName = :storeShortName, address = :detailArea,  area = :area, rcsArea = :rcsArea where  storeCode = :customerStoreNum and  recuitmentId in ( :demandIds )")
    void updateOffLineReportDetail(List<String> demandIds, String storeShortName, String rcsArea, String province, String cityName, String district, String detailArea, String area, String customerStoreNum);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_djxqstore set province = :province, city = :cityName, areas =:district,  storeName = :storeShortName, address = :detailArea,  area = :area, storeChannel = :storeOutlet, storeContacts = :customDockingPerson, contactsPhone = :customLinkType, cityType = :cityLevel where  storeCode = :customerStoreNum and  parentId in ( :demandIds )")
    void updateDJDetail(List<String> demandIds, String storeShortName,  String province, String cityName, String district, String detailArea, String area, String storeOutlet,  String customDockingPerson, String customLinkType, String cityLevel, String customerStoreNum);

    @Modifying
    @Query(nativeQuery = true , value = "update iden7_whmdxx set longitude = '', latitude = '', sssq = '' where  id = :id")
    void clearFASQ(String id);
}
